--region [dbo].[AddGroupNews]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddGroupNews]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddGroupNews]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddGroupNews]
GO

CREATE PROCEDURE [dbo].[AddGroupNews]
	@GroupId int,
	@Title nvarchar(255),
	@Introduction nvarchar(2000),
	@LinkUrl nvarchar(500),
	@Body nvarchar(max),
	@UpdateTime datetime,
	@Source nvarchar(50)
AS

INSERT INTO [GroupNews] (
	[GroupId],
	[Title],
	[Introduction],
	[LinkUrl],
	[Body],
	[UpdateTime],
	[Source]
) VALUES (
	@GroupId,
	@Title,
	@Introduction,
	@LinkUrl,
	@Body,
	@UpdateTime,
	@Source
)

GO
--endregion


--region [dbo].[UpdateGroupNews]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateGroupNews]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateGroupNews]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateGroupNews]
GO

CREATE PROCEDURE [dbo].[UpdateGroupNews]
	@Id int,
	@GroupId int,
	@Title nvarchar(255),
	@Introduction nvarchar(2000),
	@LinkUrl nvarchar(500),
	@Body nvarchar(max),
	@UpdateTime datetime,
	@Source nvarchar(50)
AS

UPDATE [GroupNews] SET
	[GroupId] = @GroupId,
	[Title] = @Title,
	[Introduction] = @Introduction,
	[LinkUrl] = @LinkUrl,
	[Body] = @Body,
	[UpdateTime] = @UpdateTime,
	[Source] = @Source
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[DelGroupNews]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelGroupNews]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelGroupNews]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelGroupNews]
GO

CREATE PROCEDURE [dbo].[DelGroupNews]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[GroupNews]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetGroupNews]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupNews]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupNews]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupNews]
GO

CREATE PROCEDURE [dbo].[GetGroupNews]
	@Id int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[Id],
	[GroupId],
	[Title],
	[Introduction],
	[LinkUrl],
	[Body],
	[UpdateTime],
	[Source]
FROM
	[GroupNews]
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[GetGroupNewsList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupNewsList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupNewsList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupNewsList]
GO

CREATE PROCEDURE [dbo].[GetGroupNewsList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [GroupNews]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[GroupNews].[Id],
	[GroupId],
	[Title],
	[GroupNews].[Introduction],
	[LinkUrl],
	[Body],
	[GroupNews].[UpdateTime],
	[Source],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[GroupNews], [Group]
WHERE
	[GroupNews].[GroupId] = [Group].[Id]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTopGroupNews]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTopGroupNews]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTopGroupNews]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTopGroupNews]
GO

CREATE PROCEDURE [dbo].[GetTopGroupNews]
	@N int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT TOP ' + STR(@N) + '
	[GroupNews].[Id],
	[GroupId],
	[Title],
	[GroupNews].[Introduction],
	[LinkUrl],
	[Body],
	[UpdateTime],
	[GroupNews].[Source]
FROM
	[GroupNews]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END
	
IF @OrderByExpression <> ''
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression 
END	

EXEC sp_executesql @SQL

GO
--endregion